const mysql = require('mysql2/promise');
const mysql_config = require("../util/mysql_config");
const crypto = require('crypto');
const moment = require('moment'); // 引入 moment 库来处理时间

const WiwiPay_withdrawal = require("../withdrawalChannel/WiwiPay_withdrawal")

// 创建连接池
const pool = mysql.createPool({
    host: mysql_config.host,
    user: mysql_config.user,
    password: mysql_config.password,
    database: 'new_slot',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
    dateStrings: true // 所有 DATE/TIME 返回字符串，不转 Date
});

// 初始化开启 event_scheduler
(async () => {
    let connection;
    try {
        connection = await pool.getConnection();
        await connection.query("SET GLOBAL event_scheduler = ON");
        console.log("✅数据库 Event Scheduler 已开启");
    } catch (err) {
        console.error("❌ 启用 Event Scheduler 失败：", err.message);
    } finally {
        if (connection) connection.release();
    }
})();

// 基础查询封装
async function query(sql, params) {
    const [rows] = await pool.query(sql, params);
    return rows;
}

// --------------------- 导出查询 ---------------------

exports.query_houtai = query;
exports.query_game = query;
exports.pool = pool;

// --------------------- 登录 ---------------------

exports.login = async (data, ip) => {
    const { username, password } = data;
    const updatePlayerSql = `
        UPDATE players SET isOnline = 1, lastLoginIp = ?, last_login_time = NOW() WHERE playerId = ?
    `;
    const getEmailsSql = 'SELECT * FROM email WHERE playerId = ? AND `delete` = 0';
    const getDailySql = 'SELECT is_receive FROM daily WHERE playerId = ?';
    const insertDailySql = 'INSERT INTO daily (playerId) VALUES (?)';

    try {
        const players = await getPlayerByUsername(username);
        if (players.length === 0) throw new Error('Player not found or invalid username');

        const player = players[0];
        if (player.password !== password) throw new Error('Invalid password');

        // 删除不需要暴露给前端的字段
        delete player.password;
        delete player.total_bet;
        delete player.isOnline;
        delete player.lastLoginIp;
        delete player.last_login_time;
        delete player.totalFree;
        delete player.totalRedeem;
        delete player.total_win;
        delete player.deviceID;

        const playerId = player.playerId;
        await query(updatePlayerSql, [ip, playerId]);

        // 获取邮件信息
        const email = await query(getEmailsSql, [playerId]);

        // 查询 daily 表
        let daily = await query(getDailySql, [playerId]);

        // 如果没有找到数据，则插入一条记录
        if (daily.length === 0) {
            await query(insertDailySql, [playerId]);
            // 插入完成后再次查询
            daily = await query(getDailySql, [playerId]);
        }

        return { respCode: "SUCCESS", data: { player, email, daily } };
    } catch (err) {
        console.log('登录失败:', err);
        return { respCode: "FAIL", data: err.message };
    }
};


// --------------------- 注册 ---------------------

exports.register = async (data) => {
    const { username, password, coin } = data;
    const insertUserSql = 'INSERT INTO players (username, password, coin) VALUES (?, ?, ?)';
    const interRechargeSql = 'INSERT INTO betjihe (playerId, amount, success, isFree, jindu, time) VALUES (?, ?, 0, 1, 0, NOW())';

    try {
        const existingUsers = await getPlayerByUsername(username);
        if (existingUsers.length > 0) throw new Error('Username already exists');

        const insertResult = await query(insertUserSql, [username, password, coin]);
        const playerId = insertResult.insertId;

        await query(interRechargeSql, [playerId, coin]);

        return { respCode: "SUCCESS", data: { username } };
    } catch (err) {
        console.log('注册失败:', err);
        return { respCode: "FAIL", data: err.message };
    }
};

// --------------------- 修改头像 ---------------------

exports.changeHead = async (data) => {
    const { playerId, headId } = data;
    const checkSql = "SELECT playerId FROM players WHERE playerId = ?";
    const updateSql = "UPDATE players SET headid = ? WHERE playerId = ?";

    try {
        const rows = await query(checkSql, [playerId]);
        if (rows.length === 0) return { respCode: "FAIL", data: "Player not found." };

        await query(updateSql, [headId, playerId]);
        return { respCode: "SUCCESS", data: { headId } };
    } catch (err) {
        console.log('修改头像失败:', err);
        return { respCode: "FAIL", data: err.message };
    }
};

// --------------------- 修改密码 ---------------------

exports.changePsw = async (data) => {
    const { playerId, oldPsw, newPsw } = data;
    const checkSql = "SELECT playerId, password FROM players WHERE playerId = ?";
    const updateSql = "UPDATE players SET password = ? WHERE playerId = ?";

    try {
        const rows = await query(checkSql, [playerId]);
        if (rows.length === 0) return { respCode: "FAIL", data: "Player not found." };

        const player = rows[0];
        if (player.password !== oldPsw) return { respCode: "FAIL", data: "Old password is incorrect." };

        await query(updateSql, [newPsw, playerId]);
        return { respCode: "SUCCESS", data: "Password updated successfully." };
    } catch (err) {
        console.log('修改密码失败:', err);
        return { respCode: "FAIL", data: err.message };
    }
};

// --------------------- 加载配置 ---------------------

exports.loadConfig = async () => {
    const gameSql = "SELECT gameId, gameName, isHot, isNew, isShow, name FROM game";
    const messageSql = "SELECT * FROM message";
    const vipBonusSql = "SELECT value FROM config WHERE name = 'vipBonus'";

    try {
        const [gameRows, messageRows, vipBonusRows] = await Promise.all([
            query(gameSql),
            query(messageSql),
            query(vipBonusSql)
        ]);

        return {
            respCode: "SUCCESS",
            data: {
                games: gameRows,
                message: messageRows,
                vipBonus: vipBonusRows.length > 0 ? vipBonusRows[0].value : null
            }
        };
    } catch (err) {
        console.log('加载配置失败:', err);
        return {
            respCode: "FAIL",
            data: {
                games: err.message,
                message: err.message,
                vipBonus: err.message
            }
        };
    }
};

// --------------------- 获取投注记录 ---------------------

exports.getBetRecord = async (data) => {
    const { playerId } = data;
    const sql = `SELECT * FROM betjihe WHERE playerId = ?`;

    try {
        const ret = await query(sql, [playerId]);
        return { respCode: "SUCCESS", data: ret };
    } catch (err) {
        console.log('获取投注记录失败:', err);
        return { respCode: "FAIL", message: err };
    }
};

// --------------------- 获取投注历史 ---------------------

exports.getBetHistory = async (data) => {
    const { playerId, day, gameType } = data;
    const daysNum = Number(day) || 1;
    let sql = `SELECT * FROM betHistory WHERE playerId = ? AND betTime BETWEEN DATE_SUB(NOW(), INTERVAL ? DAY) AND NOW()`;

    if (gameType && gameType !== 'all') {
        sql += ` AND gameType = ?`;
    }

    try {
        const params = [playerId, daysNum];
        if (gameType && gameType !== 'all') params.push(gameType);

        const ret = await query(sql, params);
        return { respCode: "SUCCESS", data: ret };
    } catch (err) {
        console.log('获取投注历史记录失败:', err);
        return { respCode: "FAIL", message: err };
    }
};

// --------------------- 改变玩家在线状态 ---------------------
exports.changePlayerStatus = async (playerId, status) => {
    const sql = `UPDATE players SET isOnline = ? WHERE playerId = ?`;
    try {
        const ret = await query(sql, [status, playerId]);
        return { respCode: "SUCCESS", data: ret };
    } catch (err) {
        console.log('改变玩家在线状态失败:', err);
        return { respCode: "FAIL", message: err };
    }
};

// --------------------- 用户登出 ---------------------

exports.userLoginOut = async (playerId) => {
    const sql = `UPDATE players SET isOnline = 0 WHERE playerId = ?`;
    try {
        const ret = await query(sql, [playerId]);
        return { respCode: "SUCCESS", data: ret };
    } catch (err) {
        console.log('用户登出失败:', err);
        return { respCode: "FAIL", message: err };
    }
};

// --------------------- 读取邮件 ---------------------

exports.readEmail = async (data) => {
    const { emailId, playerId } = data;
    const updateSql = "UPDATE email SET `read` = 1 WHERE id = ?";
    const selectSql = "SELECT * FROM email WHERE id = ? AND playerId = ?";

    try {
        await query(updateSql, [emailId]);
        const emailData = await query(selectSql, [emailId, playerId]);

        if (emailData.length === 0) {
            return { respCode: "FAIL", message: "邮件不存在或不属于该玩家" };
        }

        return { respCode: "SUCCESS", data: emailData[0] };
    } catch (err) {
        console.log('读取邮件失败:', err);
        return { respCode: "FAIL", message: err };
    }
};

// --------------------- 领取邮件金币 ---------------------

exports.receiveEmail = async (data) => {
    const { emailId } = data;
    const connection = await pool.getConnection();

    try {
        await connection.beginTransaction();

        // 1. 检查邮件是否存在且未领取，取出 playerId、coin、coinType
        const [emailCheckRows] = await connection.query(
            "SELECT playerId, coin, coinType, `read` FROM email WHERE id = ? FOR UPDATE",
            [emailId]
        );
        const email = emailCheckRows[0];
        if (!email) {
            await connection.rollback();
            return { respCode: "FAIL", message: "邮件不存在" };
        }
        if (email.read === 1) {
            await connection.rollback();
            return { respCode: "FAIL", message: "该邮件已被领取" };
        }

        const { playerId, coin, coinType } = email;

        // 2. 给玩家加金币
        const [updatePlayerResult] = await connection.query(
            "UPDATE players SET coin = coin + ? WHERE playerId = ?",
            [coin, playerId]
        );
        if (!updatePlayerResult.affectedRows) {
            await connection.rollback();
            return { respCode: "FAIL", message: "发放金币失败" };
        }

        // 3. 插入 betjihe 表
        const now = new Date();
        const target = coinType === 1 ? coin * 10 : coinType === 2 ? coin * 3 : 0;
        const isFree = coinType === 1 ? 1 : coinType === 2 ? 0 : null;

        await connection.query(
            `INSERT INTO betjihe (playerId, amount, coin, target, success, isFree, jindu, time)
             VALUES (?, ?, ?, ?, 0, ?, 0, ?)`,
            [playerId, coin / 100, coin, target, isFree, now]
        );

        // 4. 更新邮件状态为已领取
        const [updateEmailResult] = await connection.query(
            "UPDATE email SET `read` = 1 WHERE id = ?",
            [emailId]
        );
        if (!updateEmailResult.affectedRows) {
            await connection.rollback();
            return { respCode: "FAIL", message: "更新邮件状态失败" };
        }

        // 5. 查询玩家领取后的金币余额
        const [playerRows] = await connection.query(
            "SELECT coin FROM players WHERE playerId = ?",
            [playerId]
        );
        const coinNow = playerRows[0]?.coin ?? 0;

        // 6. 查询邮件详情（完整记录）
        const [emailRows] = await connection.query(
            "SELECT * FROM email WHERE id = ?",
            [emailId]
        );
        const emailInfo = emailRows[0] || null;

        await connection.commit();

        return {
            respCode: "SUCCESS",
            message: "领取成功",
            coin: coinNow,
            coinType,
            emailId,
            email: emailInfo
        };
    } catch (err) {
        await connection.rollback();
        console.log("领取邮件失败:", err);
        return {
            respCode: "FAIL",
            message: "领取失败",
            error: err
        };
    } finally {
        connection.release();
    }
};



// --------------------- 删除邮件 ---------------------

exports.deleteEmail = async (data) => {
    const { playerId, emailId } = data;
    const updateSql = "UPDATE email SET `delete` = 1 WHERE id = ? AND playerId = ?";
    const selectSql = "SELECT * FROM email WHERE id = ? AND playerId = ?";

    try {
        await query(updateSql, [emailId, playerId]);
        const emailData = await query(selectSql, [emailId, playerId]);

        if (emailData.length === 0) {
            return { respCode: "FAIL", message: "邮件不存在或不属于该玩家" };
        }

        return { respCode: "SUCCESS", data: emailData[0] };
    } catch (err) {
        console.error("删除邮件失败:", err);
        return { respCode: "FAIL", message: err };
    }
};

// --------------------- 每日转盘获取或新增 ---------------------

exports.getDaily = async (playerId) => {
    const checkSql = 'SELECT * FROM daily WHERE playerId = ?';
    const insertSql = 'INSERT INTO daily (playerId) VALUES (?)';

    try {
        const result = await query(checkSql, [playerId]);
        if (result.length > 0) return result[0];

        await query(insertSql, [playerId]);
        const newResult = await query(checkSql, [playerId]);
        return newResult[0];
    } catch (error) {
        console.error('获取或插入数据失败:', error);
        throw error;
    }
};

// --------------------- 每日转盘更新 ---------------------

exports.updateDaily = async (playerId, coin, zhuan) => {
    const sql = `UPDATE daily SET daily = 1, coin = ?, zhuan = ? WHERE playerId = ?`;
    try {
        const result = await query(sql, [coin * 100, zhuan, playerId]);
        return result;
    } catch (err) {
        console.error('更新 daily 表失败:', err);
        throw err;
    }
};

// --------------------- 获取提现配置 ---------------------

exports.getRedeemConfig = async (data) => {
    const { playerId, agentId } = data;
    try {
        // 递归查询 withdrawal_channel
        let currentAgentId = agentId;
        let configRows = [];

        while (currentAgentId) {
            const configSql = "SELECT * FROM `withdrawal_channel` WHERE is_show = 1 AND agent_id = ?";
            configRows = await query(configSql, [currentAgentId]);

            if (configRows.length > 0) break;

            // 查找上级代理
            const upSql = "SELECT up, agent_level FROM agent WHERE agent_id = ?";
            const agentResult = await query(upSql, [currentAgentId]);

            if (agentResult.length === 0) break;

            const { up, agent_level } = agentResult[0];

            if (agent_level === 0 || !up) break;

            currentAgentId = up; // 继续向上查
        }

        // 查询玩家 cashTag
        const playerSql = "SELECT cashTag FROM `players` WHERE playerId = ?";
        const playerRows = await query(playerSql, [playerId]);
        const cashTag = playerRows.length > 0 ? playerRows[0].cashTag : null;

        return { config: configRows, cashTag };
    } catch (error) {
        console.error('获取兑换配置或cashTag出错:', error);
        return { config: [], cashTag: null };
    }
};


// --------------------- 修改cashTag ---------------------

exports.changeCashTag = async (data) => {
    const { playerId, cashTag } = data;
    try {
        const sql = "UPDATE players SET cashTag = ? WHERE playerId = ?";
        await query(sql, [cashTag, playerId]);
        return { respCode: "SUCCESS", message: "修改成功" };
    } catch (error) {
        console.error('修改cashTag出错:', error);
        return [];
    }
};

// --------------------- 绑定账号 ---------------------
exports.bindAccount = async (data) => {
    try {
        const { playerId, username, password } = data;

        // 1. 检查用户名是否存在
        const checkSql = 'SELECT playerId FROM players WHERE username = ?';
        const existingPlayer = await query(checkSql, [username]);

        if (existingPlayer.length > 0) {
            return {
                respCode: "FAIL",
                message: "Account already exists. Please use a different account."
            };
        }

        // 2. 更新数据到表，包括设置isGuest为0
        const updateSql = 'UPDATE players SET username = ?, password = ?, isGuest = 0 WHERE playerId = ?';
        const result = await query(updateSql, [username, password, playerId]);

        if (result.affectedRows === 1) {
            return {
                respCode: "SUCCESS",
                message: "Account linked successfully!"
            };
        } else {
            return {
                respCode: "FAIL",
                message: "PlayerId does not exist. Account binding failed."
            };
        }
    } catch (error) {
        console.error('绑定账号出错:', error);
        return {
            respCode: "FAIL",
            message: "Binding failed: Server error."
        };
    }
};


// --------------------- 获取客服链接 ---------------------
exports.getKefuUrl = async (data) => {
    try {
        const { agentId } = data;
        let currentAgentId = agentId;
        let result = [];

        while (currentAgentId) {
            // 先查当前 agentId 对应的客服配置
            const sql = "SELECT * FROM kefu WHERE agent_id = ?";
            result = await query(sql, [currentAgentId]);

            if (result.length > 0) break;

            // 查询上级代理信息
            const upSql = "SELECT up, agent_level FROM agent WHERE agent_id = ?";
            const agentInfo = await query(upSql, [currentAgentId]);

            if (agentInfo.length === 0) break;

            const { up, agent_level } = agentInfo[0];

            if (agent_level === 0 || !up) break;

            currentAgentId = up;
        }

        return {
            respCode: "SUCCESS",
            data: result
        };
    } catch (error) {
        console.error(`[${new Date().toISOString()}] 获取客服链接出错:`, error.message);
        return {
            respCode: "FAIL",
            message: "获取客服链接失败，请稍后再试"
        };
    }
};

//--------------------- 获取活动 ---------------------
exports.getEventData = async () => {
    // 只查询当前时间在 startTime 和 stopTime 之间，或者 stopTime 为 null 的活动
    const sql = `
        SELECT * FROM event
        WHERE NOW() >= startTime
          AND (NOW() <= stopTime OR stopTime IS NULL)
    `;
    try {
        const result = await query(sql);
        return {
            respCode: "SUCCESS",
            data: result
        };
    } catch (error) {
        return {
            respCode: "FAIL",
            message: "获取活动列表失败，请稍后再试"
        };
    }
};

// --------------------- 获取充值通道 和最近的充值订单---------------------
exports.getRechargeChannel = async (data) => {
    try {
        const { playerId, agentId } = data || {};
        if (!playerId) {
            return {
                respCode: "FAIL",
                message: "playerId不能为空"
            };
        }

        // 递归查询可用的充值通道
        let currentAgentId = agentId;
        let channels = [];

        while (currentAgentId) {
            const channelSql = "SELECT * FROM recharge_channel WHERE isShow = 1 AND agent_id = ?";
            channels = await query(channelSql, [currentAgentId]);

            if (channels.length > 0) break;

            // 如果没有查到，再查 agent 表获取上级
            const upSql = "SELECT up, agent_level FROM agent WHERE agent_id = ?";
            const agentResult = await query(upSql, [currentAgentId]);

            if (agentResult.length === 0) break;

            const { up, agent_level } = agentResult[0];

            if (agent_level === 0 || !up) break;

            currentAgentId = up;  // 继续查上级
        }

        // 查询30分钟内未完成的充值订单
        const orderSql = `
            SELECT *
            FROM recharge_order
            WHERE playerId = ?
              AND time >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
              AND isOK = 0
        `;
        const orders = await query(orderSql, [playerId]);

        return {
            respCode: "SUCCESS",
            data: {
                channels,
                recentOrders: orders.length > 0 ? orders : null
            }
        };
    } catch (error) {
        console.error("getRechargeChannel 出错:", error);
        return {
            respCode: "FAIL",
            message: "获取充值通道或订单失败，请稍后再试"
        };
    }
};

// --------------------- 写入充值订单 WiwiPay---------------------
exports.insertRechargeOrder_WiwiPay = async (data, url, isFirst, payOrderNo, sign) => {
    try {
        const playerId = data.wayParam?.clientId;
        const orderId = data.mchOrderNo;
        const amount = parseFloat(data.amount) / 100; // 转换为实际金额
        const moneyCoin = Math.round(amount * 100);   // 金币数
        freeCoin = data.extParam * 100;

        const is_success = 0; // 初始状态：未完成
        const pingtai = 'WiwiPay';
        const wayCode = data.wayCode || '';
        const currency = data.currency || 'USD';
        const time = new Date(); // 当前时间

        const sql = `
            INSERT INTO recharge_order 
            (playerId, orderId, amount, moneyCoin, freeCoin, is_success, pingtai, wayCode, currency, time, url,is_first,pay_order_no,sign) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?)
        `;

        const params = [playerId, orderId, amount, moneyCoin, freeCoin, is_success, pingtai, wayCode, currency, time, url, isFirst, payOrderNo, sign];

        const result = await query(sql, params);
        return { success: true, insertId: result.insertId };

    } catch (error) {
        console.error('insertRechargeOrder_WiwiPay error:', error);
        return { success: false, error: error.message };
    }
};

// --------------------- 充值成功回调---------------------
exports.rechargeCallback = async (orderId) => {
    const connection = await pool.getConnection();
    try {
        await connection.beginTransaction();
        const now = new Date();

        // 1. 查找订单
        const [rows] = await connection.query(
            `SELECT * FROM recharge_order WHERE orderId = ?`,
            [orderId]
        );

        if (rows.length === 0) {
            await connection.rollback();
            return { success: false, message: '没有查询到该订单' };
        }

        const order = rows[0];

        // 检查是否重复回调
        if (order.is_success === 1) {
            await connection.rollback();
            return { success: false, message: '订单已处理', playerId: order.playerId };
        }

        // // 更新订单状态为成功
        // await connection.query(
        //     `UPDATE recharge_order SET is_success = 1 WHERE orderId = ?`,
        //     [orderId]
        // );

        // 2. 插入邮件：充值成功
        await insertEmail(connection, order.playerId, 'Top-up Successful', 'Your top-up has been credited.', order.moneyCoin, 2, now);

        // 如果 freeCoin 不为 0，再插入一条邮件（赠送金币）
        if (order.freeCoin && Number(order.freeCoin) !== 0) {
            await insertEmail(connection, order.playerId, 'Gifted Bonus Coins', 'Thank you for your top-up, we have credited you with bonus coins.', order.freeCoin, 1, now);
        }

        // 3. 查询 daily 表对应的 playerId 的 daily 和 is_receive 字段
        const [dailyRows] = await connection.query(
            `SELECT daily, coin, is_receive FROM daily WHERE playerId = ? FOR UPDATE`,
            [order.playerId]
        );

        if (dailyRows.length > 0 && dailyRows[0].daily === 1 && dailyRows[0].is_receive === 0) {
            const dailyCoin = dailyRows[0].coin;

            await insertEmail(connection, order.playerId, 'Daily Sign-in Bonus Coins', 'Thank you for your top-up, here are your sign-in bonus coins. Please claim them.', dailyCoin, 1, now);

            await connection.query(
                `UPDATE daily SET is_receive = 1 WHERE playerId = ?`,
                [order.playerId]
            );
        }

        // 4. 修改 players 表 total_recharge 和 vip
        const [playerRows] = await connection.query(
            `SELECT total_recharge FROM players WHERE playerId = ? FOR UPDATE`,
            [order.playerId]
        );
        const oldTotalRecharge = playerRows[0]?.total_recharge || 0;
        const newTotal = oldTotalRecharge + parseFloat(order.amount);

        // 更新订单状态为成功
        if (oldTotalRecharge === 0) {
            await connection.query(
                `UPDATE recharge_order SET is_chu = 1, is_success = 1 WHERE orderId = ?`,
                [orderId]
            );
        } else {
            await connection.query(
                `UPDATE recharge_order SET is_success = 1 WHERE orderId = ?`,
                [orderId]
            );
        }


        // 5. 更新玩家总充值和 VIP
        let newVip = 0;
        if (newTotal >= 100000) newVip = 10;
        else if (newTotal >= 80000) newVip = 9;
        else if (newTotal >= 50000) newVip = 8;
        else if (newTotal >= 20000) newVip = 7;
        else if (newTotal >= 10000) newVip = 6;
        else if (newTotal >= 5000) newVip = 5;
        else if (newTotal >= 1000) newVip = 4;
        else if (newTotal >= 500) newVip = 3;
        else if (newTotal >= 100) newVip = 2;
        else if (newTotal >= 20) newVip = 1;

        await connection.query(
            `UPDATE players SET total_recharge = ?, vip = ? WHERE playerId = ?`,
            [newTotal, newVip, order.playerId]
        );

        // 6. 首充标记
        if (order.is_first === 1) {
            await connection.query(
                `UPDATE players SET is_first_recharge = 1 WHERE playerId = ?`,
                [order.playerId]
            );
        };

        // 7. 计算推广奖励
        await calculateReferralReward(order.playerId, oldTotalRecharge, order.amount, orderId, connection);

        await connection.commit();
        return { success: true, message: '回调处理成功', playerId: order.playerId };

    } catch (error) {
        await connection.rollback();
        console.error('rechargeCallback error:', error);
        return { success: false, message: '回调处理失败', error: error.message };
    } finally {
        connection.release();
    }
};

// --------------------- 计算推广奖励 ---------------------
async function calculateReferralReward(playerId, oldTotalRecharge, amount, orderId, connection) {
    // 查询 config 表中 name='fan_yong' 的 value 字段
    const [rows] = await connection.query(
        `SELECT value FROM config WHERE name = ?`,
        ['fan_yong']
    );

    if (rows.length === 0) {
        console.log('未找到 fan_yong 配置');
        return;
    }

    const valueArray = JSON.parse(rows[0].value);  // 分成配置
    const now = new Date();
    let inviterIds = [];    // 存储邀请者id
    let currentPlayerId = playerId;

    for (let i = 0; i < 3; i++) { // 往上查三层
        const [rows] = await connection.query(
            `SELECT inviter_id FROM players WHERE playerId = ?`,
            [currentPlayerId]
        );

        if (rows.length === 0 || !rows[0].inviter_id) {
            // 没有上级了，提前退出
            break;
        }

        currentPlayerId = rows[0].inviter_id;
        inviterIds.push(currentPlayerId);
    }

    if (oldTotalRecharge === 0) {
        // 给三层邀请人都分
        for (let i = 0; i < inviterIds.length; i++) {
            const inviterId = inviterIds[i];
            const rewardAmount = parseInt(amount * valueArray[i]);

            await connection.query(
                `INSERT INTO referral_reward 
                 (playerId, inviter_id, amount, order_id, create_time, settled) 
                 VALUES (?, ?, ?, ?, ?, 0)`,
                [playerId, inviterId, rewardAmount, orderId, now]
            );

            console.log(`已给第${i + 1}层邀请人 ${inviterId} 增加奖励:`, rewardAmount);
        }
    } else if (inviterIds.length > 0) {
        // 只给一级邀请人分
        const firstInviterId = inviterIds[0];
        const rewardAmount = parseInt(amount * valueArray[0]);

        await connection.query(
            `INSERT INTO referral_reward 
             (playerId, inviter_id, amount, order_id, create_time, settled) 
             VALUES (?, ?, ?, ?, ?, 0)`,
            [playerId, firstInviterId, rewardAmount, orderId, now]
        );

        console.log(`已给一级邀请人 ${firstInviterId} 增加奖励:`, rewardAmount);
    }
};



// --------------------- 快速开始 ---------------------

exports.quickStart = async (data, ip) => {
    try {
        const { username, password, deviceID, inviterId, agentId } = data;

        const loginRes1 = await loginByUsername(username, password, deviceID, ip);
        if (loginRes1) return { ...loginRes1, from: "login" };

        if (deviceID !== "ios" && deviceID !== "web") {
            const loginRes2 = await loginByDeviceID(deviceID, ip);
            if (loginRes2) return { ...loginRes2, from: "login" };
        }

        const newUser = await registerNewPlayer(ip, deviceID, inviterId, agentId);
        const loginRes3 = await exports.login({ username: newUser.username, password: newUser.password, deviceID }, ip);
        return { ...loginRes3, from: "register", ...newUser };

    } catch (err) {
        console.log('quickStart 出错:', err);
        return { respCode: "FAIL", data: err.message };
    }
};


// --------------------- 私有方法 ---------------------

async function loginByUsername(username, password, deviceID, ip) {
    const players = await getPlayerByUsername(username);
    if (players.length > 0 && players[0].password === password) {
        return await exports.login({ username, password, deviceID }, ip);
    }
    return null;
}

async function registerNewPlayer(ip, deviceID, inviterId = null, agentId = null) {
    // 统一处理 inviterId 和 agentId
    inviterId = Number(inviterId) > 0 ? Number(inviterId) : null;
    agentId = Number(agentId) > 0 ? Number(agentId) : null;

    let freeCoinValue = 0;
    if (deviceID !== "web") {
        const configRows = await query("SELECT value FROM config WHERE name = 'freeCoin'");
        if (configRows.length > 0) freeCoinValue = Number(configRows[0].value) || 0;
    }

    // 验证 inviterId 是否存在 players 表中，没有则设 null
    if (inviterId !== null) {
        const inviterRows = await query("SELECT playerId FROM players WHERE playerId = ?", [inviterId]);
        if (inviterRows.length === 0) {
            inviterId = null;
        }
    }

    // 验证 agentId 是否存在 agent 表中，没有则设 null
    if (agentId !== null) {
        const agentRows = await query("SELECT agent_id FROM agent WHERE agent_id = ?", [agentId]);
        if (agentRows.length === 0) {
            agentId = null;
        }
    }

    // 如果有 inviterId 而 agentId 为空，则查询 inviterId 对应的 agentId
    if (inviterId !== null && agentId === null) {
        const agentRows = await query("SELECT agent_id FROM players WHERE playerId = ?", [inviterId]);
        if (agentRows.length > 0 && agentRows[0].agent_id !== null) {
            agentId = agentRows[0].agent_id;
        }
    }

    const insertSql = `INSERT INTO players 
        (username, password, create_time, isGuest, createIp, deviceID, coin, inviter_id, agent_id) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;
    const updateSql = 'UPDATE players SET username = ? WHERE playerId = ?';

    const plainPassword = crypto.randomBytes(6).toString('hex').slice(0, 6);
    const md5Password = crypto.createHash('md5').update(plainPassword).digest('hex').toUpperCase();
    const now = new Date();

    const insertResult = await query(insertSql, [
        'guest_temp',
        md5Password,
        now,
        1,
        ip,
        deviceID,
        freeCoinValue * 100,
        inviterId,
        agentId
    ]);
    const newPlayerId = insertResult.insertId;

    const guestUsername = `guest_${newPlayerId}`;
    await query(updateSql, [guestUsername, newPlayerId]);

    if (deviceID !== "web" && freeCoinValue > 0) {
        const amount = freeCoinValue;
        const coin = amount * 100;
        const target = coin * 10;
        const insertRechargeSql = 'INSERT INTO betjihe (playerId, amount, coin, target, isFree, time) VALUES (?, ?, ?, ?, ?, ?)';
        await query(insertRechargeSql, [newPlayerId, amount, coin, target, 1, now]);

        // 插入邮件
        const insertEmailSql = 'INSERT INTO email (playerId, title, content, coin, mailType, create_time) VALUES (?, ?, ?, ?, ?, ?)';
        const title = "Free Coins";
        const content = `Congratulations! You have received ${amount} free coins, already credited.`;
        await query(insertEmailSql, [newPlayerId, title, content, 0, 1, now]);
    }

    return { username: guestUsername, password: md5Password };
}

async function loginByDeviceID(deviceID, ip) {
    const players = await getPlayerByDeviceID(deviceID);
    if (players.length > 0) {
        const player = players[0];
        return await exports.login({ username: player.username, password: player.password, deviceID }, ip);
    }
    return null;
}

async function getPlayerByUsername(username) {
    const sql = 'SELECT * FROM players WHERE username = ?';
    return await query(sql, [username]);
}

async function getPlayerByDeviceID(deviceID) {
    const sql = 'SELECT * FROM players WHERE deviceID = ?';
    return await query(sql, [deviceID]);
}

// --------------------- 快速开始 ---------------------


// --------------------- 修改数据库的isOK字段 ---------------------
exports.changeIsOK = async (data) => {
    try {
        const { orderId } = data;
        const sql = "UPDATE recharge_order SET isOK = 1 WHERE orderId = ?";
        await query(sql, [orderId]);
        return {
            respCode: "SUCCESS",
            message: "修改成功"
        };
    } catch (error) {
        console.error('修改isOK出错:', error);
        return {
            respCode: "FAIL",
        }
    }
}

// --------------------- 获取玩家充值记录 ---------------------
exports.getRechargeRecord = async (data) => {
    try {
        const { playerId } = data;
        const sql = "SELECT * FROM recharge_order WHERE playerId = ?";
        const ret = await query(sql, [playerId]);
        return {
            respCode: "SUCCESS",
            data: ret
        };
    } catch (error) {
        console.error('获取充值记录失败:', error);
        return { respCode: "FAIL", message: error.message };
    }
}

// --------------------- 获取玩家VIP信息 ---------------------
exports.getVipInfo = async (data) => {
    try {
        const { playerId } = data;

        // 查询是否已有记录
        const sql = "SELECT * FROM vip_bonus WHERE playerId = ?";
        const ret = await query(sql, [playerId]);

        // 如果不存在，就插入一条默认记录
        if (ret.length === 0) {
            const insertSql = `
                INSERT INTO vip_bonus (playerId)
                VALUES (?)`;
            await query(insertSql, [playerId]);

            // 再次查询刚插入的记录
            const [newData] = await query(sql, [playerId]);
            return {
                respCode: "SUCCESS",
                data: newData
            };
        }

        // 如果有记录，直接返回
        return {
            respCode: "SUCCESS",
            data: ret[0]
        };
    } catch (error) {
        console.error('获取VIP信息失败:', error);
        return { respCode: "FAIL", message: error.message };
    }
};

//领取VIP奖励
exports.receiveVipBonus = async (data) => {
    try {
        const { playerId, bonus, vip } = data;

        // 校验 vip 等级，防止 SQL 注入
        if (!/^(?:[1-9]|10)$/.test(String(vip))) {
            throw new Error("Invalid VIP level");
        }

        // 更新 vip_bonus 表
        const column = `vip${vip}`;
        const updateSql = `UPDATE vip_bonus SET ${column} = 1 WHERE playerId = ?`;
        await query(updateSql, [playerId]);

        // 插入邮件记录
        const now = new Date();
        await insertEmail(
            pool,                 // 使用连接池
            playerId,             // 玩家 ID
            "VIP Reward",         // 邮件标题
            "Dear VIP user, this is your upgrade reward.", // 邮件内容
            bonus,                // 奖励金币
            1,                    // coinType: 1 表示免费
            now                   // 创建时间
        );

        // 查询更新后的完整记录
        const selectSql = `SELECT * FROM vip_bonus WHERE playerId = ?`;
        const [updatedData] = await query(selectSql, [playerId]);

        return { respCode: "SUCCESS", message: "领取VIP奖励成功", data: updatedData };
    } catch (error) {
        console.error("领取VIP奖励失败:", error);
        return { respCode: "FAIL", message: error.message };
    }
};


// 独立的金币邮件插入方法   
async function insertEmail(connection, playerId, title, content, coin, coinType, now) {
    const mailType = 2; // 金币邮件
    await connection.query(
        `INSERT INTO email (playerId, title, content, coin, mailType, create_time, \`read\`, coinType, \`delete\`)
         VALUES (?, ?, ?, ?, ?, ?, 0, ?, 0)`,
        [playerId, title, content, coin, mailType, now, coinType]
    );
};

// --------------------- 获取该玩家总充值 ---------------------
exports.getTotalRecharge = async (data) => {
    try {
        const { playerId } = data;
        const sql = `SELECT total_recharge, is_first_recharge,coin FROM players WHERE playerId = ?`;
        const ret = await query(sql, [playerId]);
        return {
            respCode: "SUCCESS",
            data: ret[0]
        };
    } catch (error) {
        console.log('获取用户充值总额及首充状态失败:', error);
        return { respCode: "ERROR", message: "获取失败" };
    }
};

// --------------------- 绑定代理 ---------------------
exports.bindAgent = async (data) => {
    try {
        const { playerId, info } = data;

        if (!playerId || !info) {
            return { respCode: "ERROR", message: "playerId or agentId is missing" };
        }

        // Check if agent exists in agents table
        const checkSql = "SELECT COUNT(*) AS count FROM `agent` WHERE `agent_id` = ?";
        const [result] = await query(checkSql, [info]);
        if (result.count === 0) {
            return { respCode: "ERROR", message: "Invalid agent ID" };
        }

        // Update players table
        const sql = "UPDATE `players` SET `agent_id` = ? WHERE `playerId` = ?";
        await query(sql, [info, playerId]);

        return { respCode: "SUCCESS", message: "Agent binding successful", type: data.type, info: data.info };
    } catch (error) {
        console.log('Agent binding failed:', error);
        return { respCode: "ERROR", message: "Agent binding failed" };
    }
};

// --------------------- 绑定邀请人 ---------------------
exports.bindInviter = async (data) => {
    try {
        const { playerId, info } = data;  // info 是邀请人的 playerId

        if (!playerId || !info) {
            return { respCode: "ERROR", message: "Invalid link" };
        }

        // 根据邀请人的 playerId 查出他的 agent_id
        const getAgentSql = "SELECT agent_id FROM players WHERE playerId = ?";
        const [inviterRecord] = await query(getAgentSql, [info]);
        if (!inviterRecord || !inviterRecord.agent_id) {
            return { respCode: "ERROR", message: "Invalid link" };
        }
        const inviterAgentId = inviterRecord.agent_id;

        // 更新当前玩家的 agent_id 为邀请人的 agent_id，inviter_id 为邀请人的 playerId
        const updateSql = "UPDATE players SET agent_id = ?, inviter_id = ? WHERE playerId = ?";
        await query(updateSql, [inviterAgentId, info, playerId]);

        return { respCode: "SUCCESS", message: "Binding successful", type: data.type, info: data.info };
    } catch (error) {
        console.log('Agent and inviter binding failed:', error);
        return { respCode: "ERROR", message: "Invalid link" };
    }
};

// --------------------- 获取玩家邮件 ---------------------
exports.getPlayerMails = async (playerId) => {
    try {
        const result = await query('SELECT * FROM email WHERE playerId = ? AND `delete` = 0', [playerId]);
        return {
            respCode: "SUCCESS",
            data: result
        };
    } catch (error) {
        console.log('获取邮件出错:', error);
        return {
            respCode: "FAIL",
            message: error.message
        };
    }
};

// --------------------- 获取首充信息 ---------------------
exports.getFirstRechargeInfo = async (data) => {
    const { playerId, agentId } = data;

    try {
        // 查找玩家是否有 且 is_success = 0 且 isOK = 0 且在半小时内的订单
        const [firstRechargeOrder] = await query(
            `SELECT * FROM recharge_order 
             WHERE playerId = ? 
               AND is_success = 0 
               AND isOK = 0 
               AND time >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
             LIMIT 1`,
            [playerId]
        );
        // 递归查找首充通道
        const getFirstRechargeChannel = async (currentAgentId) => {
            const channelResult = await query(
                'SELECT * FROM recharge_channel WHERE agent_id = ? AND is_first = 1 LIMIT 1',
                [currentAgentId]
            );

            if (channelResult.length > 0) {
                return channelResult[0];
            } else {
                // 没找到，查找上级代理
                const [agentInfo] = await query(
                    'SELECT up FROM agent WHERE agent_id = ? LIMIT 1',
                    [currentAgentId]
                );
                if (agentInfo && agentInfo.up) {
                    return await getFirstRechargeChannel(agentInfo.up);
                } else {
                    return null;
                }
            }
        };

        const firstRechargeChannel = await getFirstRechargeChannel(agentId);

        return {
            respCode: "SUCCESS",
            data: {
                firstRechargeOrder: firstRechargeOrder || null,
                firstRechargeChannel: firstRechargeChannel || null
            }
        };
    } catch (error) {
        console.error('获取首充信息出错:', error);
        return {
            respCode: "FAIL",
            message: error.message
        };
    }
};

// --------------------- 存储提现订单 ---------------------
exports.insertWithdrawalOrder_WiwiPay = async (playerId, params, data, mchNo, cashTag) => {
    const connection = await pool.getConnection(); // 获取支持事务的连接
    try {
        const order_id = data.mchOrderNo;
        const transfer_order_no = data.transferOrderNo;
        const sign = params.sign;
        const amount = params.amount / 100;
        const pingtai = 'WiwiPay';
        const wayCode = params.wayCode;
        const currency = params.currency;
        const time = new Date();

        await connection.beginTransaction();

        // 1. 查询玩家余额
        const [rows] = await connection.query(`SELECT coin FROM players WHERE playerId = ? FOR UPDATE`, [playerId]);
        if (rows.length === 0) {
            await connection.rollback();
            return {
                respCode: "FAIL",
                message: "玩家不存在"
            };
        }
        const currentCoin = rows[0].coin;

        // 2. 校验余额是否足够
        if (currentCoin < params.amount) {
            await connection.rollback();
            return {
                respCode: "FAIL",
                message: "余额不足"
            };
        }

        // 3. 扣减余额
        const sqlUpdate = `UPDATE players SET coin = coin - ? WHERE playerId = ?`;
        await connection.query(sqlUpdate, [params.amount, playerId]);

        // 4. 插入提现订单
        const sqlInsert = `INSERT INTO withdrawal_order (playerId, order_id, transfer_order_no, sign, amount, pingtai, wayCode, currency, time, mchNo, cashTag) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
        await connection.query(sqlInsert, [playerId, order_id, transfer_order_no, sign, amount, pingtai, wayCode, currency, time, mchNo, cashTag]);

        // 5. 查询更新后的余额
        const [newRows] = await connection.query(`SELECT coin FROM players WHERE playerId = ?`, [playerId]);
        const updatedCoin = newRows[0].coin;

        await connection.commit();

        return {
            respCode: "SUCCESS",
            updatedCoin
        };
    } catch (error) {
        await connection.rollback();
        console.log('存储提现订单并更新余额出错:', error);
        return {
            respCode: "FAIL",
            message: error.message
        };
    } finally {
        connection.release();
    }
};

// --------------------- 提现回调 ---------------------
exports.withdrawalCallback = async (data) => {
    try {
        const order_id = data.mchOrderNo;
        const state = data.state;

        // 更新 withdrawal_order 的 state
        const updateSql = `UPDATE withdrawal_order SET state = ? WHERE order_id = ?`;
        await query(updateSql, [state, order_id]);

        // 查询 playerId 和 amount
        const selectSql = `SELECT playerId, amount FROM withdrawal_order WHERE order_id = ?`;
        const result = await query(selectSql, [order_id]);

        if (result.length === 0) {
            console.warn(`未找到 order_id 为 ${order_id} 的记录`);
            return;
        }

        const playerId = result[0].playerId;
        const amount = result[0].amount;

        // 如果 state 为 3/4/5/6，退还金币
        if (["3", "4", "5", "6", 3, 4, 5, 6].includes(state)) {
            const refundAmount = amount * 100;
            const updatePlayerSql = `UPDATE players SET coin = coin + ? WHERE playerId = ?`;
            await query(updatePlayerSql, [refundAmount, playerId]);
        }

        // 调用邮件方法
        await insertOrderEmail(playerId, order_id, state);

        return {
            success: true,
            playerId: playerId
        };
    } catch (error) {
        console.error('提现回调处理失败:', error);
    }
};


// --------------------- 查询用户余额 ---------------------
exports.getPlayerCoin = async (playerId) => {
    try {
        const [rows] = await query(`SELECT coin FROM players WHERE playerId = ?`, [playerId]);
        if (rows.length === 0) {
            return null;
        }
        return rows.coin;
    } catch (error) {
        console.error('查询用户余额出错:', error);
        return null;
    }
};

// --------------------- 查询用户提现订单  同时查询一下状态 ---------------------
exports.getOrderRecord = async (data) => {
    try {
        const playerId = data.playerId;

        // 第一次查询提现订单
        const ret = await query(`SELECT * FROM withdrawal_order WHERE playerId = ?`, [playerId]);

        // 检查状态并更新订单
        const tasks = ret.map(async (element) => {
            if ((element.state == 0 || element.state == 1) && element.pingtai === "WiwiPay") {
                await WiwiPay_withdrawal.getOrderStatus(element);
            }
        });
        await Promise.all(tasks);

        // 再次查询提现订单（获取最新状态）
        const result = await query(`SELECT * FROM withdrawal_order WHERE playerId = ?`, [playerId]);

        // 查询玩家金币
        const playerInfo = await query(`SELECT coin FROM players WHERE playerId = ?`, [playerId]);
        const coin = playerInfo.length > 0 ? playerInfo[0].coin : 0;

        return {
            respCode: "SUCCESS",
            data: result,
            coin: coin
        };
    } catch (error) {
        console.error('查询用户提现订单出错:', error);
        return {
            respCode: "FAIL",
            message: error.message
        };
    }
};

// --------------------- 更新提现订单状态 ---------------------
exports.updateWithdrawalOrder_WiwiPay = async (order_id, data) => {
    try {
        const newState = data.state;

        // 查询旧的 state 和 playerId
        const selectSql = `SELECT state, amount, playerId FROM withdrawal_order WHERE order_id = ?`;
        const result = await query(selectSql, [order_id]);

        if (result.length === 0) {
            console.warn(`未找到 order_id 为 ${order_id} 的订单`);
            return;
        }

        const { state: oldState, playerId, amount } = result[0];

        // 只在 state 从 0 或 1 变为其他状态时更新
        if ((oldState == 0 || oldState == 1) && oldState != newState) {
            const updateSql = `UPDATE withdrawal_order SET state = ? WHERE order_id = ?`;
            await query(updateSql, [newState, order_id]);

            // 如果 newState 为 3/4/5/6，退还金币
            if (["3", "4", "5", "6", 3, 4, 5, 6].includes(newState)) {
                const refundAmount = amount * 100;
                const updatePlayerSql = `UPDATE players SET coin = coin + ? WHERE playerId = ?`;
                await query(updatePlayerSql, [refundAmount, playerId]);
            }
            // 调用邮件方法
            await insertOrderEmail(playerId, order_id, newState);
        }
    } catch (error) {
        console.error('更新提现订单状态出错:', error);
    }
};
// --------------------- 提现订单邮件 ---------------------
async function insertOrderEmail(playerId, order_id, state) {
    // 统一将 state 转为字符串
    const stateKey = String(state);

    // 定义状态对应的标题和内容
    const stateMap = {
        "0": { title: "Order Created", content: `Your Redeem order ${order_id} has been created.` },
        "1": { title: "Processing Payment", content: `Your Redeem order ${order_id} is being processed.` },
        "2": { title: "Payment Success", content: `Your Redeem order ${order_id} has been successfully processed.` },
        "3": { title: "Payment Failed", content: `Your Redeem order ${order_id} has failed. The coins have been returned to your account.` },
        "4": { title: "Order Canceled", content: `Your Redeem order ${order_id} has been canceled. The coins have been returned to your account.` },
        "5": { title: "Order Refunded", content: `Your Redeem order ${order_id} has been refunded. The coins have been returned to your account.` },
        "6": { title: "Order Closed", content: `Your Redeem order ${order_id} has been closed. The coins have been returned to your account.` }
    };

    // 获取对应的标题和内容
    const { title, content } = stateMap[stateKey] || {
        title: "Redeem Status Update",
        content: `Your Redeem order ${order_id} status has been updated.`
    };

    // 插入 email 表
    const insertSql = `
        INSERT INTO email (playerId, coin, mailType, title, content, create_time)
        VALUES (?, 0, 1, ?, ?, NOW())
    `;
    await query(insertSql, [playerId, title, content]);
}

// --------------------- 查询所有下级玩家信息 ---------------------
exports.getAllSons = async (data) => {
    try {
        const playerId = data.playerId;

        // 查询玩家的 create_time
        const player = await query(`SELECT create_time FROM players WHERE playerId = ?`, [playerId]);
        if (player.length === 0) {
            return {
                respCode: "FAIL",
                message: "玩家不存在"
            };
        }
        const playerCreateTime = moment(player[0].create_time);  // 玩家注册时间

        // 计算当前日期与本周周一的日期
        const startOfCurrentWeek = moment().startOf('week'); // 当前周的周一
        const endOfCurrentWeek = moment().endOf('week');     // 当前周的周日

        // 查询待结算的奖励（根据周次查询）
        const pendingRewards = [];

        // 往回查询 8 周的数据（包括当前周）
        for (let i = 0; i < 8; i++) {
            const startDate = startOfCurrentWeek.clone().subtract(i, 'weeks').startOf('week');
            const endDate = startOfCurrentWeek.clone().subtract(i, 'weeks').endOf('week');

            // 如果查询的周已经到达或早于玩家注册的那一周，停止查询
            if (endDate.isBefore(playerCreateTime)) {
                break;  // 停止查询
            }

            const rewards = await query(
                `SELECT * FROM referral_reward WHERE inviter_id = ? AND create_time BETWEEN ? AND ? AND settled = 0`,
                [playerId, startDate.format('YYYY-MM-DD HH:mm:ss'), endDate.format('YYYY-MM-DD HH:mm:ss')]
            );

            // 即使没有数据，也需要返回空数据
            pendingRewards.push({
                week: `${startDate.format('YYYY-MM-DD')} - ${endDate.format('YYYY-MM-DD')}`,
                data: rewards.length > 0 ? rewards : [] // 如果没有数据，返回空数组
            });
        }

        // 查询一级子玩家
        const level1 = await query(`SELECT playerId FROM players WHERE inviter_id = ?`, [playerId]);
        const level1Ids = level1.map(p => p.playerId);

        // 查询二级子玩家
        let level2Ids = [];
        if (level1Ids.length > 0) {
            const level2 = await query(`SELECT playerId FROM players WHERE inviter_id IN (?)`, [level1Ids]);
            level2Ids = level2.map(p => p.playerId);
        }

        // 查询三级子玩家
        let level3Ids = [];
        if (level2Ids.length > 0) {
            const level3 = await query(`SELECT playerId FROM players WHERE inviter_id IN (?)`, [level2Ids]);
            level3Ids = level3.map(p => p.playerId);
        }

        return {
            respCode: "SUCCESS",
            data: {
                level1: level1Ids.length,
                level2: level2Ids.length,
                level3: level3Ids.length,
                pendingRewards: pendingRewards // 返回每周的奖励数据（即使没有数据，依然返回空数组）
            }
        };
    } catch (error) {
        console.error('查询子玩家及奖励出错:', error);
        return {
            respCode: "FAIL",
            message: error.message
        };
    }
};

// --------------------- 查询指定时间内充值的玩家 ---------------------
exports.getRechargeSon = async (data) => {
    try {
        const { playerId, date } = data;

        // 初始化返回结果的对象
        const result = {};

        // 查找 players 表中 inviter_id = playerId 的所有 playerId
        const playerRows = await query(
            `SELECT playerId FROM players WHERE inviter_id = ?`,
            [playerId]
        );

        // 获取所有符合条件的子玩家 playerId
        const playerIds = playerRows.map(row => row.playerId);

        if (playerIds.length === 0) {
            // 如果没有子玩家，直接返回空结果
            return result;
        }

        // 遍历每个日期范围，查询符合条件的数据数量
        for (let i = 0; i < date.length; i++) {
            const dateRange = date[i];
            const dateRanges = dateRange.split(' - '); // 日期范围格式: "2025-08-17 - 2025-08-23"
            let startDate = dateRanges[0]; // 起始日期
            let endDate = dateRanges[1]; // 结束日期

            // 将 startDate 设置为当天的 00:00:00
            startDate = moment(startDate).startOf('day').format('YYYY-MM-DD HH:mm:ss');

            // 将 endDate 设置为当天的 23:59:59
            endDate = moment(endDate).endOf('day').format('YYYY-MM-DD HH:mm:ss');

            // 查询符合条件的充值记录数量，使用子玩家的 playerId
            const rows = await query(
                `SELECT COUNT(*) AS count FROM recharge_order 
                WHERE playerId IN (?) 
                AND is_success = 1 
                AND is_chu = 1 
                AND time BETWEEN ? AND ?`,
                [playerIds, startDate, endDate]
            );

            // 处理查询结果，如果查询到数据
            if (rows && rows[0]) {
                result[dateRange] = rows[0].count;
            } else {
                result[dateRange] = 0; // 如果没有找到符合条件的记录，返回 0
            }
        }

        return result;

    } catch (error) {
        console.error('getRechargeSon error:', error);
        return { success: false, message: '查询失败', error: error.message };
    }
};
